# coding: utf-8
import os
import random
import set_env_path

from sqlalchemy import create_engine
from sqlalchemy.sql import text

engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def main():
    # Step 1 根据手机号查询用户ID和企业ID查找用户id和企业id
    find_uid_epid_sql = text("""
select id, user_id from enterprises where user_id in (
select id from user where mobile in (
    '15840397297',
	'18802477551',
	'17390001333',
	'13578916672',
	'17758887830',
	'13074517877',
	'18636605699',
	'18686003890',
	'18732103333',
	'15030190087',
	'13641336389',
	'15026698878',
	'18969127575',
	'18858313888',
	'18013913111',
	'13851694193',
	'18629246563',
	'17719502550',
	'13893421777',
	'18693109206',
	'13579909201',
	'18195800017',
	'18609500895',
	'18971555571',
	'15902792811',
	'13507919300',
	'15083559703',
	'15159555689',
	'13358305085',
	'19907487001',
	'13875885048',
	'18982006321',
	'15002868180',
	'13984057103',
	'15086808882',
	'18323381208',
	'13489291111',
	'13529119825',
	'13987127257',
	'15507887435',
	'15385511088',
	'15966644444',
	'18653953565',
	'18638683888',
	'13838017230')
)
    """)
    tmp_results = conn.execute(find_uid_epid_sql).fetchall()
    area_ep_map = {}
    user_ep_map = {}
    user_list = []
    area_list = []

    for tmp_result in tmp_results:
        user_ep_map[tmp_result.user_id] = tmp_result.id
        user_list.append(tmp_result.user_id)

        # Step2 根据企业ID查询运营中心企业关注地区
        find_aid_ep_id_sql = text("""
        select concat(substring(area_id, 1, 2),'0000') area_id, enterprise_id from enterprise_to_area
        where enterprise_id=:sql_2_res group by area_id, enterprise_id
        """)
        sql_2_results = conn.execute(
            find_aid_ep_id_sql,
            sql_2_res=tmp_result.id
        ).fetchall()
        # Step2.1构造地区ID，企业ID数据 {'area_id':'enterprise_id1,enterprise_id2'}
        for sql_2_result in sql_2_results:
            # Step2.2整理关注省份信息
            tmp_area_id = int(sql_2_result[0])
            area_ep_map.setdefault(tmp_area_id, set()).add(sql_2_result.enterprise_id)
            if int(sql_2_result[0]) not in area_list:
                area_list.append(int(sql_2_result[0]))

    # Step3 查出user_info中的所有根节点用户
    find_app_root_invite_user = text("""
    select user_id, operation_center_id
    from user_info
    where app_first_login_time
    is not null and app_root_invite_user_id = 0
        """)
    tmp_results = conn.execute(find_app_root_invite_user).fetchall()

    for tmp_result in tmp_results:
        # Step4 更新根节点所属运营中心
        # Step4.1如果查出的根节点用户ID在运营中心用户列表中，直接更新运营中心ID为自己的企业ID
        if tmp_result.user_id in user_list:
            sql_4 = text("""
            update user_info set user_info.operation_center_id = :operation_center_id
            where user_id = :tmp_user_id
            """)
            conn.execute(
                sql_4,
                operation_center_id=user_ep_map[tmp_result.user_id],
                tmp_user_id=tmp_result.user_id
            )
        else:
            # Step4.2如果查出的根节点用户ID不在运营中心用户列表中
            # 通过查出的用户ID查询用户手机归属地
            find_area_by_user_id = text("""
            select phone_area_id from user where id = :user_id_list
            """)
            res = conn.execute(
                find_area_by_user_id,
                user_id_list=tmp_result.user_id,
            ).fetchall()
            for i in res:
                # Step4.2.1如果用户手机归属地在运营中心负责区域内，随机取一个所属区域运营中心ID
                if i.phone_area_id in area_list:
                    update_operation_by_area_id = text("""
                    update user_info
                    set user_info.operation_center_id = :operation_center_id
                    where user_id = :tmp_user_id
                    """)
                    conn.execute(
                        update_operation_by_area_id,
                        operation_center_id=random.choice(list(area_ep_map[i.phone_area_id])),
                        tmp_user_id=tmp_result.user_id
                    )

                # TODO 如果用户手机归属地不在运营中心负责区域内或者没有手机归属地，暂时不做操作

    # Step5 查找根节点不为自己的用户
    find_not_app_root_invite_user = text("""
        select user_id, app_root_invite_user_id
        from user_info
        where app_first_login_time
    is not null and app_root_invite_user_id <> 0;
    """)
    not_app_root_invite_user = conn.execute(
        find_not_app_root_invite_user
    ).fetchall()
    for i in not_app_root_invite_user:
        # Step5.1 通过根节点ID找到对应的根节点用户
        find_app_root_user = text("""
        select operation_center_id
        from user_info
        where user_id = :user_id;
        """)
        app_root_user = conn.execute(
            find_app_root_user,
            user_id=i.app_root_invite_user_id
        ).fetchone()

        # Step5.2 把用户的运营中心ID更新为根节点运营中心ID
        update_operation_by_app_root_invite_id = text("""
            update user_info set user_info.operation_center_id = :operation_center_id
            where user_id = :tmp_user_id
        """)
        conn.execute(
            update_operation_by_app_root_invite_id,
            operation_center_id=app_root_user.operation_center_id,
            tmp_user_id=i.user_id
        )

if __name__ == '__main__':
    main()